﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using rInvoice.Utils;

namespace rInvoice
{
    class ServerObject
    {
        DataBridge mDataBridge = null;
        private DataTable mTableNames;
        private DataTable mTableFieldNames;

        public ServerObject()
        {
            mDataBridge = new DataBridge();

            string commandTableList = @"SELECT " + UFT.Table_TableList.COLUMN_TableID
                                            + ", " + UFT.Table_TableList.COLUMN_TableNameDB
                                            + " FROM " + UFT.Table_TableList.TABLE_NAME;

            string commandFieldList = @"SELECT " + UFT.Table_FieldsList.COLUMN_FieldID
                                            + ", " + UFT.Table_FieldsList.COLUMN_FieldNameDB
                                            + " FROM " + UFT.Table_FieldsList.TABLE_NAME;

            if (mDataBridge.Connect() == true)
            {
                mTableNames = mDataBridge.ExecuteQuery(commandTableList);
                //mLastError = mDataBridge.LastError;

                mTableFieldNames = mDataBridge.ExecuteQuery(commandFieldList);
                //mLastError = mDataBridge.LastError;
                mDataBridge.Disconnect();
            }
            //else
            //{
            //    throw new Exception("HRPM server application failed to connect to DB. " + mLastError);
            //}

        }


        private string GetTableName(int tableID)
        {
            string tableName = string.Empty;
            if (mTableNames != null && mTableNames.Rows.Count > 0)
            {
                string expression = UFT.Table_TableList.COLUMN_TableID + " = " + tableID;
                DataRow[] selectedRows = mTableNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    tableName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return tableName;
        }

        private string GetColumnName(int columnID)
        {
            string columnName = string.Empty;
            if (mTableFieldNames != null && mTableFieldNames.Rows.Count > 0)
            {
                string expression = UFT.Table_FieldsList.COLUMN_FieldID + " = " + columnID;
                DataRow[] selectedRows = mTableFieldNames.Select(expression);
                if (selectedRows != null && selectedRows.Length == 1)
                {
                    columnName = (string)selectedRows[0].ItemArray[1];
                }
                else
                {
                    throw new Exception("Multiple values for one table name code!");
                }

            }
            return columnName;
        }

        public bool AutentificateUser(string username, string password)
        {
            bool result = false;
            DataTable returnTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT UserID "
                                            + " FROM USERS "
                                            + " WHERE Login ='" + username + "' "
                                            + " AND Password ='" + password + "' ";

                    returnTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            if (returnTable != null && returnTable.Rows.Count > 0) result = true;

            return result;
        }

        #region Table

        #region Users

        public bool AddUsers(string firstName, string lastName, string login, string password, int roleTypeID, int passwordStatus, int recordStatus)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Users (FirstName, LastName, [Login], [Password], RoleType, PasswordStatus, RecordStatus, isDelete) "
                        + "VALUES ( "
                        + "'" + firstName + "' "
                        + ", '" + lastName + "' "
                        + ", '" + login + "' "
                        + ", '" + password + "' "
                        + ", " + roleTypeID + " "
                        + ", " + passwordStatus + " "
                        + ", " + recordStatus + " "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateUsers(int userID, string firstName, string lastName, string login, string password, int roleTypeID, int passwordStatus, int recordStatus, int isDelete)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Users "
                            + "SET FirstName = '" + firstName + "' "
                            + ", LastName = '" + lastName + "' "
                            + ", [Login] ='" + login + "' "
                            + ", [Password] = '" + password + "' "
                            + ", RoleType = " + roleTypeID + " "
                            + ", PasswordStatus = " + passwordStatus + " "
                            + ", RecordStatus = " + recordStatus + " "
                            + ", isDelete = " +isDelete + " "
                            + " WHERE UserID = " + userID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable SelectUsers(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT UserID "
                        + ", FirstName "
                        + ", LastName "
                        + ", Login "
                        + " FROM USERS "
                        + " WHERE isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectUsersByID(int userID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT UserID "
                        + ", FirstName "
                        + ", LastName "
                        + ", Login "
                        + ", Password "
                        + ", RoleType "
                        + ", PasswordStatus "
                        + ", RecordStatus "
                        + " FROM USERS "
                        + " WHERE isDelete = 0 "
                        + " AND UserID = " + userID
                        + " ";

                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        #endregion Users

        #region Bank Information

        public DataTable SelectBankList(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT BankID "
                        + ", BankCod "
                        + ", BankAccount "
                        + ", BankName "                        
                        + " FROM Bank "
                        + " WHERE isDelete = " + Convert.ToInt32(isDelete) + " ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectBankByID(int bankID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                        @"SELECT BankID "
                        + ", BankName "
                        + ", BankAccount "
                        + ", BankCod "
                        + " FROM Bank "
                        + " WHERE isDelete = 0 "
                        + " AND BankID = " + bankID
                        + " ";

                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        public bool AddBank(string bankCod, string bankAccount, string bankName)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Bank (BankCod, BankAccount, BankName, isDelete) "
                        + "VALUES ( "
                        + "'" + bankCod + "' "
                        + ", '" + bankAccount + "' "
                        + ", '" + bankName + "' "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateBank(int bankID, string bankCod, string bankAccount, string bankName, bool isDelete)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Bank "
                            + "SET BankCod = '" + bankCod + "' "
                            + ", BankAccount = '" + bankAccount + "' "
                            + ", BankName ='" + bankName + "' "
                            + ", isDelete = '" + Convert.ToInt32(isDelete) + "' "
                            + " WHERE BankID = " + bankID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }
        
        #endregion Bank Information

        #region Contracte Information

        public DataTable SelectContractList(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = 
                        @" SELECT     CompanyContract.ContractID "
                    + " , Company.CompanyName "
                    + " , CompanyContract.ContractName "
                    + " , CompanyContract.ContractDescription "

                    + " FROM         CompanyContract LEFT OUTER JOIN "
                    + "                        Company ON CompanyContract.CompanyID = Company.CompanyID "
                    + " WHERE     (CompanyContract.isDelete = " + Convert.ToInt32(isDelete) + ") ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectContractByID(int contractID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT ContractID "
                                            + ", CompanyID "
                                            + ", ContractName "
                                            + ", ContractDescription "
                                            + " FROM CompanyContract "
                                            + " WHERE isDelete = 0 "
                                            + " AND ContractID = " + contractID
                                            + " ";

                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        public bool AddContract(int CompanyID, string ContractName, string ContractDescription)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO CompanyContract (CompanyID, ContractName, ContractDescription, isDelete) "
                        + "VALUES ( "
                        + "'" + CompanyID + "' "
                        + ", '" + ContractName + "' "
                        + ", '" + ContractDescription + "' "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateContract(int ContractID, int CompanyID, string ContractName, string ContractDescription, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE CompanyContract "
                            + "SET CompanyID = '" + CompanyID + "' "
                            + ", ContractName = '" + ContractName + "' "
                            + ", ContractDescription ='" + ContractDescription + "' "
                            + ", isDelete = '" + Convert.ToInt32(isDeleted) + "' "
                            + " WHERE ContractID = " + ContractID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Contracte Information

        #region Product Information

        public DataTable SelectProductList(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =
                    @" SELECT     Product.ProductID "
                    + " , Product.Cod "
                    + " , Product.TypID "
                    + " , Product.ClassifierID "
                    + " , Product.ProductName "
                    + " , Product.Copyright "
                    + " , Product.CompanyProductID "
                    + " , Product.ProductYear "
                    + " , Product.ProductMonth "
                    + " , Product.ProductDay "
                    + " , Product.BarCod "
                    + " , Product.ProductNameShort "

                    + " FROM         Product "
                   // + " LEFT OUTER JOIN "
                   // + "                        Company ON CompanyContract.CompanyID = Company.CompanyID "
                    + " WHERE     (Product.isDelete = " + Convert.ToInt32(isDelete) + ") ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectProductByID(int productID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = 
                    @" SELECT     Product.ProductID "
                    + " , Product.Cod "
                    + " , Product.TypID "
                    + " , Product.ClassifierID "
                    + " , Product.ProductName "
                    + " , Product.Copyright "
                    + " , Product.CompanyProductID "
                    + " , Product.ProductYear "
                    + " , Product.ProductMonth "
                    + " , Product.ProductDay "
                    + " , Product.BarCod "
                    + " , Product.ProductNameShort "

                    + " FROM         Product "
                    + " WHERE     (Product.isDelete = 0) "
                    + " AND      (Product.ProductID = " + productID + ") ";


                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        public bool AddProduct(int Cod, int TypID, int ClassifierID, string ProductName, string Copyright, string CompanyProductID, int ProductYear, int ProductMonth, int ProductDay, string BarCod, string ProductNameShort)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Product (Cod, TypID, ClassifierID, ProductName, Copyright, CompanyProductID, ProductYear, ProductMonth, ProductDay, BarCod, ProductNameShort, isDelete) "
                        + "VALUES ( "
                        + "" + Cod + " "
                        + ", " + TypID + " "
                        + ", " + ClassifierID + " "
                        + ", '" + ProductName + "' "
                        + ", '" + Copyright + "' "
                        + ", '" + CompanyProductID + "' "
                        + ", " + ProductYear + " "
                        + ", " + ProductMonth + " "
                        + ", " + ProductDay + " "
                        + ", '" + BarCod + "' "
                        + ", '" + ProductNameShort + "' "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateProduct(int productID, int Cod, int TypID, int ClassifierID, string ProductName, string Copyright, string CompanyProductID, int ProductYear, int ProductMonth, int ProductDay, string BarCod, string ProductNameShort, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Product "
                            + "SET Cod = " + Cod + " "
                            + ", TypID = " + TypID + " "
                            + ", ClassifierID = " + ClassifierID + " "
                            + ", ProductName ='" + ProductName + "' "
                            + ", Copyright ='" + Copyright + "' "
                            + ", CompanyProductID ='" + CompanyProductID + "' "
                            + ", ProductYear = " + ProductYear + " "
                            + ", ProductMonth = " + ProductMonth + " "
                            + ", ProductDay = " + ProductDay + " "
                            + ", BarCod ='" + BarCod + "' "
                            + ", ProductNameShort ='" + ProductNameShort + "' "
                            + ", isDelete = '" + Convert.ToInt32(isDeleted) + "' "
                            + " WHERE ProductID = " + productID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Product Information

        #region CompanyAccount Information

        public DataTable SelectCompanyAccountList(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT CompanyAccount.AccountID "
                                        + ", Company.CompanyName "
                                        + ", CompanyAccount.AccountName "
                                        + ", CompanyAccount.ValutaID "
                                        + ", Bank.BankName "
                                        + ", CompanyAccount.AccountNumber "
                                        + ", CompanyAccount.AccountTrezor "
                                        + ", Bank.BankName "
                                        + ", CompanyAccount.AccountCorespodent "
                                        + ", CompanyAccount.TopID "
                                        + " FROM CompanyAccount "
                                        + " LEFT OUTER JOIN "
                                        + " Company ON CompanyAccount.CompanyID = Company.CompanyID "
                                        + " LEFT OUTER JOIN "
                                        + " Bank ON CompanyAccount.BankID = Bank.BankID "

                                        + " WHERE     (Product.isDelete = " + Convert.ToInt32(isDelete) + ") ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectCompanyAccountByID(int CompanyID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT AccountID "
                                        + ", CompanyID "
                                        + ", AccountName "
                                        + ", ValutaID "
                                        + ", BankID "
                                        + ", AccountNumber "
                                        + ", AccountTrezor "
                                        + ", BankCorespodentID "
                                        + ", AccountCorespodent "
                                        + ", TopID "
                                        + " FROM CompanyAccount "
                                        + " WHERE isDelete = 0  "
                                        + " AND CompanyID = " + CompanyID;

                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        public bool AddCompanyAccount(int CompanyID, string AccountName, int ValutaID, int BankID, string AccountNumber, string AccountTrezor, int BankCorespodentID, string AccountCorespodent, int TopID)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Product (CompanyID, AccountName, ValutaID, BankID, AccountNumber, AccountTrezor, BankCorespodentID, AccountCorespodent, TopID, isDelete) "
                        + "VALUES ( "
                        + "" + CompanyID + " "
                        + ", " + AccountName + " "
                        + ", " + ValutaID + " "
                        + ", " + BankID + " "
                        + ", '" + AccountNumber + "' "
                        + ", '" + AccountTrezor + "' "
                        + ", " + BankCorespodentID + " "
                        + ", '" + AccountCorespodent + "' "
                        + ", " + TopID + " "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateCompanyAccount(int companyAccountID, int CompanyID, string AccountName, int ValutaID, int BankID, string AccountNumber, string AccountTrezor, int BankCorespodentID, string AccountCorespodent, int TopID, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Company "
                            + "SET CompanyID = " + CompanyID + " "
                            + ", AccountName = '" + AccountName + "' "
                            + ", ValutaID = " + ValutaID + " "
                            + ", BankID = " + BankID + " "
                            + ", AccountNumber = '" + AccountNumber + "' "
                            + ", AccountTrezor = '" + AccountTrezor + "' "
                            + ", BankCorespodentID = " + BankCorespodentID + " "
                            + ", AccountCorespodent ='" + AccountCorespodent + "' "
                            + ", TopID ='" + TopID + "' "
                            + ", isDelete = '" + Convert.ToInt32(isDeleted) + "' "
                            + " WHERE CompanyID = " + companyAccountID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion CompanyAccount Information


        #region Company Information

        public DataTable SelectCompanyList(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT Company.CompanyID "
                        + ", Company.CompanyName "
                        + ", Company.RegistryDate "
                        + ", Company.RegistryNumber "
                        + ", Company.CodFisc "
                        + ", Company.CodTVA "
                        + ", Company.Phone "
                        + ", Company.Address "
                        + ", Classifiers.Name AS City "
                        + ", Classifiers_1.Name AS Country "
                        + " FROM Company INNER JOIN Classifiers "
                        + " ON Company.CityID = Classifiers.CodeID "
                        + " INNER JOIN Classifiers AS Classifiers_1 "
                        + " ON Company.CountryID = Classifiers_1.CodeID "
                        + " WHERE (Company.isDelete = " + Convert.ToInt32(isDelete) + ") "
                        + " AND (Classifiers.TypeID = 4) "
                        + " AND (Classifiers.isDelete = 0) "
                        + " AND (Classifiers_1.TypeID = 5) "
                        + " AND (Classifiers_1.isDelete = 0) "
                        + " ";
                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        public DataRow SelectCompanyByID(int companyID)
        {
            DataRow resultRow = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText =@"SELECT CompanyID "
                                        + ", CompanyName "
                                        + ", RegistryDate "
                                        + ", RegistryNumber "
                                        + ", CodFisc "
                                        + ", CodTVA "
                                        + ", Phone "
                                        + ", Address "
                                        + ", CityID "
                                        + ", CountryID "
                                        + " FROM Company "
                                        + " WHERE isDelete = 0  "
                                        + " AND CompanyID = " + companyID;

                    resultRow = mDataBridge.ExecuteQuery(commandText).Rows[0];
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultRow;
        }

        public bool AddCompany(string CompanyName, DateTime RegistryDate, string RegistryNumber, string CodFisc, string CodTVA, string Phone, string Address, int CityID, int CountryID)
        {
            bool result = false;
            int isDelete = 0;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Company (CompanyName, RegistryDate, RegistryNumber, CodFisc, CodTVA, Phone, Address, CityID, CountryID, isDelete) "
                        + "VALUES ( "
                        + "'" + CompanyName + "' "
                        + ", '" + RegistryDate.ToString("yyyy/MM/dd") + "' "
                        + ", '" + RegistryNumber + "' "
                        + ", '" + CodFisc + "' "
                        + ", '" + CodTVA + "' "
                        + ", '" + Phone + "' "
                        + ", '" + Address + "' "
                        + ", " + CityID + " "
                        + ", " + CountryID + " "
                        + ", " + isDelete + " "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);


                    string Query = @"Select CompanyID FROM Company WHERE "
                        + " CompanyName ='" + CompanyName + "' "
                        + " AND RegistryNumber = '" + RegistryNumber + "' "
                        + " AND CodFisc = '" + CodFisc + "' "
                        + " AND CodTVA = '" + CodTVA + "' "
                        + " AND Address = '" + Address + "' "
                        + " AND CityID = " + CityID + " "
                        + " AND CountryID = " + CountryID + " ";

                    DataTable resultCompanyIDTable = mDataBridge.ExecuteQuery(Query);

                    if (resultCompanyIDTable != null && resultCompanyIDTable.Rows.Count == 1)
                    {
                        string CompanyID = resultCompanyIDTable.Rows[0][0].ToString();

                        nonQuery = @"INSERT INTO CompanyAccount (CompanyID, AccountName, ValutaID, BankID, AccountNumber, AccountTrezor, BankCorespodentID, AccountCorespodent, TopID, isDelete) "
                            + "VALUES ( "
                            + "'" + CompanyID + "' "
                            + ", ' ' "
                            + ", 0 "
                            + ", 0 "
                            + ", ' ' "
                            + ", ' ' "
                            + ", 0 "
                            + ", ' ' "
                            + ", 0 "
                            + ", " + isDelete + " "
                            + " ) ";

                        result = mDataBridge.ExecuteNonQuery(nonQuery);
                    }
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateCompany(int CompanyID, string CompanyName, DateTime RegistryDate, string RegistryNumber, string CodFisc, string CodTVA, string Phone, string Address, int CityID, int CountryID, bool isDeleted)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Company "
                            + "SET CompanyName = '" + CompanyName + "' "
                            + ", RegistryDate = '" + RegistryDate + "' "
                            + ", RegistryNumber ='" + RegistryNumber + "' "
                            + ", CodFisc ='" + CodFisc + "' "
                            + ", CodTVA ='" + CodTVA + "' "
                            + ", Phone ='" + Phone + "' "
                            + ", Address ='" + Address + "' "
                            + ", CityID ='" + CityID + "' "
                            + ", CountryID ='" + CountryID + "' "
                            + ", isDelete = '" + Convert.ToInt32(isDeleted) + "' "
                            + " WHERE CompanyID = " + CompanyID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }


        #endregion Company Information

        #region ClassifierType

        public bool AddClassifierType(string name)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO ClassifierTypes ([Name]) "
                        + "VALUES ( "
                        + " '" + name + "' "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifierType(int typeID, string nameType)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE ClassifierTypes "
                            + "SET ClassifierTypes.[Name] = '" + nameType + "' "
                            + " WHERE ClassifierTypes.TypeID = " + typeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable SelectTypeClassifiers(bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT TypeID "
                        + ", Name "
                        + " FROM ClassifierTypes "
                        + " WHERE isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion ClassifierType

        #region Classifiers

        public bool AddClassifiers(int typeID, string name, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Classifiers (TypeID, [Name], Description) "
                        + "VALUES ( "
                        + typeID
                        + ", '" + name + "' "
                        + ", '" + description + "' "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateClassifiers(int codeID, int typeID, string nameClassifiers, string description)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Classifiers "
                            + "SET Classifiers.[TypeID] = " + typeID + " "
                            + ", Classifiers.[Name] = '" + nameClassifiers + "' "
                            + ", Classifiers.[Description] ='" + description + "' "
                            + " WHERE Classifiers.CodeID = " + codeID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetClassifiersListByTypeID(int typeID)
        {
            DataTable classifierTypesByIDList = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Code) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] "
                                         + ", Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Description) + "] "
                                         + " FROM " + GetTableName(UFT.Table_Classifiers.TABLE_NAME) + " as Classifiers "
                                        + " WHERE Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_TypeID) + "] = " + typeID + " ";
                    commandText += " ORDER BY Classifiers.[" + GetColumnName(UFT.Table_Classifiers.COLUMN_Name) + "] asc ";
                    classifierTypesByIDList = mDataBridge.ExecuteQuery(commandText);
                    // mLastError = mDataBridge.LastError;
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return classifierTypesByIDList;
        }

        public DataTable SelectClassifiers(int typeID, bool isDelete)
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT Classifiers.CodeID "
                        + ", Classifiers.Name "
                        + ", Classifiers.Description "
                        + ", ClassifierTypes.Name AS Grupe "
                        + " FROM Classifiers INNER JOIN ClassifierTypes "
                        + " ON Classifiers.TypeID = ClassifierTypes.TypeID "
                        + " WHERE Classifiers.isDelete = " + Convert.ToInt32(isDelete) + " "
                        + " ";
                    if (typeID > 1)
                    {
                        commandText += " AND Classifiers.TypeID = " + typeID;
                    }

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion Classifiers

        #region Constant

        public bool AddConstant(string mIdentificator, string mSinonim, string mComent)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @"INSERT INTO Constant ([Identificator], [Sinonim], [Coment]) "
                        + "VALUES ( "
                        + "'" + mIdentificator + "' "
                        + ", '" + mSinonim + "' "
                        + ", '" + mComent + "' "
                        + " ) ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public bool UpdateConstant(int constantID, string mIdentificator, string mSinonim, string mComent)
        {
            bool result = false;

            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string nonQuery = @" UPDATE Constant "
                            + "SET [Identificator] = '" + mIdentificator + "' "
                            + ", [Sinonim] = '" + mSinonim + "' "
                            + ", [Coment] ='" + mComent + "' "
                            + " WHERE ConstantID = " + constantID + " "
                            + " ";

                    result = mDataBridge.ExecuteNonQuery(nonQuery);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            return result;
        }

        public DataTable GetConstantList()
        {
            DataTable resultTable = null;
            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT ConstantID "
                        + ", Identificator "
                        + ", Sinonim "
                        + ", Coment "
                        + " FROM Constant "                       
                        + " ";                    

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion Constant

        #region _Document

        public DataTable SelectDocumentListByTipID(int typeID)
        {
            DataTable resultTable = null;
            bool isDelete = false;
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    string commandText = @"SELECT DocumentInput.Period "
                        + ", DocumentInput.DocumentNumber "
                        + ", Company.CompanyName "
                        + ", SUM(InputTable.Price * InputTable.Cant) AS DocSum "
                        + " FROM DocumentInput "
                        + " INNER JOIN InputTable "
                        + " ON DocumentInput.DocumentInputID = InputTable.DocumentInputID "
                        + " INNER JOIN Company "
                        + " ON DocumentInput.CompanyID = Company.CompanyID "
                        + " WHERE (InputTable.isDelete = " + Convert.ToInt32(isDelete) + ") "
                        + " AND (DocumentInput.isDelete = " + Convert.ToInt32(isDelete) + ") "
                        + " AND (DocumentInput.TypeID = " + typeID + ") "
                        + " GROUP BY DocumentInput.DocumentNumber "
                        + ", DocumentInput.Period "
                        + ", Company.CompanyName "
                        + " ORDER BY DocumentInput.DocumentNumber DESC ";

                    resultTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }
            return resultTable;
        }

        #endregion _Document

        #endregion Table

        public bool GetDoubleCheckUsers(string mLoghin)
        {
            bool result = false;
            DataTable returnTable = new DataTable();

            try
            {
                if (mDataBridge.Connect() == true)
                {

                    string commandText = @"SELECT UserID "
                                            + " FROM USERS "
                                            + " WHERE Login ='" + mLoghin + "' ";

                    returnTable = mDataBridge.ExecuteQuery(commandText);
                }
            }
            finally
            {
                mDataBridge.Disconnect();
            }

            if (returnTable.Rows.Count == 0) result = true;

            return result;
        }
    }
}
